-- ===============================================
-- 
-- Configure Data Access Quick Starts Transfer Database 
-- Process : Create table, update fields
-- Create by : utehn.s@gmail.com
-- Date : 11/09/2008 : 5:00PM
-- 
-- ===============================================

USE [Transfer]
GO
/****** Object:  Table [dbo].[Transcourseno]    Script Date: 11/02/2008 23:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if not exists (select * from sysobjects where id = object_id(N'[Transcourseno_transfered]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [Transcourseno_transfered](
		[no] [int] IDENTITY(1,1) NOT NULL,
		[id] [nvarchar](9) NOT NULL,
		[courseno] [nvarchar](6) NOT NULL,
		[insertDate] [datetime] NULL CONSTRAINT [DF_Transcourseno_transfered_insertDate]  DEFAULT (getdate()),
		[title] [nvarchar](30) NULL,
		[credit] [nvarchar](15) NULL,
		[grade] [nvarchar](2) NULL,
		[Idpre] [bit] NULL,
		[Namepre] [nvarchar](6) NULL,
		[TRM] [nvarchar](3) NULL,
		[Term] [nvarchar](1) NULL,
		[Year] [nvarchar](4) NULL,
		[status] [nvarchar](2) NULL,
		[crelec] [float] NULL,
		[crelab] [float] NULL,
		[Doc1_1] [nvarchar](1) NULL,
		[Doc2_1] [nvarchar](1) NULL,
		[Doc3_1] [nvarchar](1) NULL,
		[Doc4_1] [nvarchar](1) NULL,
		[grade_Pre1] [nvarchar](2) NULL,
		[crelec_Pre1] [nvarchar](2) NULL,
		[crelab_Pre1] [nvarchar](2) NULL,
		[total_course1] [int] NULL,
		[total_cousePre1] [int] NULL,
		[total_credit1] [int] NULL,
		[total_Crepre1] [int] NULL,
		[course2_old] [nvarchar](10) NULL,
		[crelab2_old] [nvarchar](2) NULL,
		[crelec2_old] [nvarchar](2) NULL,
		[grade2_old] [nvarchar](2) NULL,
		[total_course3] [int] NULL,
		[total_Credit3] [int] NULL,
		[credit_unit] [nvarchar](2) NULL,
	CONSTRAINT [PK_Transcourseno_transfered] PRIMARY KEY CLUSTERED 
	(
	[no] ASC,
	[id] ASC,
	[courseno] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
END

GO 

DECLARE @table_name sysname, 
		@column_name sysname,
		@hasrow int

--- Credit_unit
SET @table_name = 'Transcourseno'
SET @column_name = 'Credit_unit' 
SET @hasrow = ( SELECT   COUNT(convert(sysname, c.name))
FROM     syscolumns c, sysobjects o
Where   c.id = o.id 
	AND convert(sysname, o.name) = @table_name
        AND convert(sysname, c.name) = @column_name)
if ( @HasRow = 0 )
BEGIN 
	ALTER TABLE Transcourseno Add credit_unit nvarchar(20) NULL    
END

--- Term
SET @table_name = 'Transcourseno'
SET @column_name = 'Term' 
SET @hasrow = ( SELECT   COUNT(convert(sysname, c.name))
FROM     syscolumns c, sysobjects o
Where   c.id = o.id 
	AND convert(sysname, o.name) = @table_name
        AND convert(sysname, c.name) = @column_name)
if ( @HasRow = 0 )
BEGIN 
	ALTER TABLE Transcourseno Add Term varchar(1) NULL   
END

-- Year
SET @table_name = 'Transcourseno'
SET @column_name = 'Year' 
SET @hasrow = ( SELECT   COUNT(convert(sysname, c.name))
FROM     syscolumns c, sysobjects o
Where   c.id = o.id 
	AND convert(sysname, o.name) = @table_name
        AND convert(sysname, c.name) = @column_name)
if ( @HasRow = 0 )
BEGIN 
	ALTER TABLE Transcourseno Add [Year] varchar(4) NULL   
END

-- Trm
SET @table_name = 'Transcourseno'
SET @column_name = 'Trm'
SET @hasrow = ( SELECT   COUNT(convert(sysname, c.name))
FROM     syscolumns c, sysobjects o
Where   c.id = o.id 
	AND convert(sysname, o.name) = @table_name
        AND convert(sysname, c.name) = @column_name)
if ( @HasRow = 0 )
BEGIN 
	ALTER TABLE Transcourseno Add Trm varchar(3) NULL    
END

-- Status
SET @table_name = 'Transcourseno'
SET @column_name = 'Status'
SET @hasrow = ( SELECT   COUNT(convert(sysname, c.name))
FROM     syscolumns c, sysobjects o
Where   c.id = o.id 
	AND convert(sysname, o.name) = @table_name
        AND convert(sysname, c.name) = @column_name)
if ( @HasRow = 0 )
BEGIN 
	ALTER TABLE Transcourseno Add [status] varchar(1) NULL    
END
